﻿Imports System.Data.SqlClient
Imports System.IO

Public Class frmProspectsFileNew

    Dim FolderPath As String = FTPAddress + "Prospects"

    Sub Clear()
        Me.txtProspName.Clear()
        Me.txtTele.Clear()
        Me.txtFax.Clear()
        Me.txtcompanyEmail.Clear()
        Me.txtWebsite.Clear()
        Me.GridContactPersons.Rows.Clear()
        Me.txtAddress.Clear()
        Me.txtCoDetails.Clear()
        Me.txtMobile.Clear()
        Me.txtProspName.Focus()
    End Sub

    Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
        Me.Close()
    End Sub

    Private Function ValidateProspectDuplication() As Boolean
        Try
            Dim cmd As New SqlCommand("Select IsNull(Count(*),0) From Prospects Where Name=N'" & Me.txtProspName.Text & "'", cnn)
            Dim X As Boolean

            cnn.Open()
            X = CBool(cmd.ExecuteScalar.ToString)
            cnn.Close()

            If X = True Then
                Me.ErrorProv.SetError(Me.txtProspName, "Prospect is already exist !")
                Return True
            End If

            Return False
            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Function

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        Me.ErrorProv.Clear()
        Try
           If Me.txtcompanyEmail.Text.Trim.Length > 0 And _
           Not System.Text.RegularExpressions.Regex.IsMatch(Me.txtcompanyEmail.Text.Trim, _
                                                            "^[a-zA-Z][\w\.-]*[a-zA-Z0-9]@[a-zA-Z0-9][\w\.-]*[a-zA-Z0-9]\.[a-zA-Z][a-zA-Z\.]*[a-zA-Z]$") Then
                Me.ErrorProv.SetError(Me.txtcompanyEmail, "Please enter a valid email address or leave blank")
                Me.txtcompanyEmail.Focus()
                Exit Sub

            Else
                Me.Cursor = Cursors.WaitCursor

                Dim Trans As SqlTransaction
                Dim cmd As New SqlCommand()

                cnn.Open()
                cmd.Connection = cnn
                Trans = cnn.BeginTransaction
                cmd.Transaction = Trans

                'Insert prospect details
                cmd.Parameters.Clear()
                cmd.CommandText = "Insert Into Prospects (Branch,Name,Tele,Mobile,Fax,Email,Website,Address,CoRegDetails," & _
                                    "UserName,TransDate) Values" & _
                                  "(@Branch,@Name,@Tele,@Mobile,@Fax,@Email,@Website,@Address,@CoRegDetails," & _
                                  "@UserName,GetDate()) select SCOPE_IDENTITY()"
                cmd.Parameters.Clear()
                cmd.Parameters.AddWithValue("@Branch", Branch)
                cmd.Parameters.AddWithValue("@Name", Me.txtProspName.Text.Trim)
                cmd.Parameters.AddWithValue("@Tele", Me.txtTele.Text.Trim)
                cmd.Parameters.AddWithValue("@Mobile", Me.txtMobile.Text.Trim)
                cmd.Parameters.AddWithValue("@Fax", Me.txtFax.Text.Trim)
                cmd.Parameters.AddWithValue("@Email", Me.txtcompanyEmail.Text.Trim)
                cmd.Parameters.AddWithValue("@Website", Me.txtWebsite.Text.Trim)
                cmd.Parameters.AddWithValue("@Address", Me.txtAddress.Text)
                cmd.Parameters.AddWithValue("@CoRegDetails", Me.txtCoDetails.Text.Trim)
                cmd.Parameters.AddWithValue("@UserName", CurrentUser)

                Dim SNo As Integer
                SNo = CInt(cmd.ExecuteScalar)

                'inserting contact persons details
                For Each row As DataGridViewRow In GridContactPersons.Rows
                    If Not row.IsNewRow Then

                        cmd.Parameters.Clear()
                        cmd.CommandText = "Insert Into ProspectContacts (ProspID,ContactName,Position,Email,Tele,SavedUser) Values (@ProspID,@ContactName,@Position,@Email,@Tele,@SavedUser)"
                        cmd.Parameters.AddWithValue("@ProspID", SNo)
                        cmd.Parameters.AddWithValue("@ContactName", row.Cells("ContactPerson").Value)
                        cmd.Parameters.AddWithValue("@Position", row.Cells("Position").Value)
                        cmd.Parameters.AddWithValue("@Email", row.Cells("Email").Value)
                        cmd.Parameters.AddWithValue("@Tele", row.Cells("Tele").Value)
                        cmd.Parameters.AddWithValue("@SavedUser", CurrentUser)
                        cmd.ExecuteNonQuery()
                    End If
                Next

                'Save(Files)
                '1) Create directory to store files
                Dim Dir As New DirectoryInfo(FolderPath)
                Dir.CreateSubdirectory(SNo)

                '2) Copy files to server
                For Each row As DataGridViewRow In Me.GridFiles.Rows
                    Dim File As New FileInfo(row.Cells(Me.DGFilesPath.Index).Value)
                    File.CopyTo(FolderPath & "\" & SNo & "\" & File.Name)
                Next

                Trans.Commit()
                cnn.Close()

                SelProspectID = SNo
                SelProspectName = Me.txtProspName.Text.Trim

                MsgBox("Saved Successfuly")

                Me.Close()

                Dim a As New frmProspectsCasesNew
                a.txtProspID.Text = SelProspectID
                a.txtProspName.Text = SelProspectName
                a.ShowDialog()

            End If
            Me.Cursor = Cursors.Default
        Catch ex As Exception
            MsgBox(ex.ToString)
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            Me.Cursor = Cursors.Default
        End Try
    End Sub

    Private Sub Button8_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClear.Click
        Clear()
    End Sub

    Private Sub GridContactPerson_CellClick(sender As System.Object, e As System.Windows.Forms.DataGridViewCellEventArgs) Handles GridContactPersons.CellClick
        If e.ColumnIndex = 4 Then
            If MsgBox("Confirm delete?", MsgBoxStyle.YesNo) = MsgBoxResult.Yes Then
                Me.GridContactPersons.Rows.RemoveAt(e.RowIndex)
            End If
        End If
    End Sub

    Private Sub txtProspName_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtProspName.TextChanged
        ErrorProv.Clear()
        ValidateProspectDuplication()
    End Sub

    Private Sub frmProspectsFileNew_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        Me.GridContactPersons.Rows.Add("", "Owner", "", "", "Delete")
        Me.GridContactPersons.Rows.Add("", "Financial Manager", "", "", "Delete")
    End Sub

    Private Sub GridFiles_CellClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles GridFiles.CellClick
        If e.ColumnIndex = Me.DGFilesColDocOpen.Index Then
            Try
                Me.Cursor = Cursors.WaitCursor

                System.Diagnostics.Process.Start(Me.GridFiles.CurrentRow.Cells("DGFilesPath").Value)

                Me.Cursor = Cursors.Default
            Catch ex As Exception
                Me.Cursor = Cursors.Default
                MsgBox(ex.ToString)
            End Try

        ElseIf e.ColumnIndex = Me.DGFilesColDocDel.Index Then
            If MsgBox("Confirm delete?", MsgBoxStyle.YesNo) = MsgBoxResult.Yes Then
                Me.GridFiles.Rows.RemoveAt(e.RowIndex)
            End If
        End If
    End Sub

    Private Sub GridContactPersons_CellEndEdit(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles GridContactPersons.CellEndEdit
        If e.ColumnIndex = Me.ContactPerson.Index Then
            Try

                Me.GridContactPersons.CurrentRow.Cells(Me.Email.Index).Value = "-"
                Me.GridContactPersons.CurrentRow.Cells(Me.Tele.Index).Value = "-"
                Me.GridContactPersons.CurrentRow.Cells(Me.Position.Index).Value = "-"

                    Me.Cursor = Cursors.Default

            Catch ex As Exception
                Me.Cursor = Cursors.Default
                MsgBox(ex.ToString)
            End Try
        End If
    End Sub

    Private Sub btnRegisterNewrProject_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRegisterNewrProject.Click
        Try
            Me.OpenFileDialog1.Filter = "All files (*.*)|*.*"
            If Me.OpenFileDialog1.ShowDialog = Windows.Forms.DialogResult.OK Then
                Me.GridFiles.Rows.Add(New String() {Me.OpenFileDialog1.FileName, Me.OpenFileDialog1.SafeFileName, "Open", "Delete"})
            End If
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub
End Class